﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MemberManagement.Common
{
    public class Helper
    {
        /// <summary>
        /// 获取Web.Config中的AppSetting中节点的值
        /// </summary>
        /// <param name="key">AppSetting中节点键</param>
        /// <param name="defValue">默认值</param>
        /// <returns></returns>
        public static string GetAppSettingValue(string key, string defValue = null)
        {
            if (string.IsNullOrEmpty(key)) return defValue;
            var value = ConfigurationManager.AppSettings[key];
            if (string.IsNullOrEmpty(value)) return defValue;
            return value;
        }

        /// <summary>
        /// 获取Web.Config中的AppSetting中节点的值
        /// </summary>
        /// <param name="key">AppSetting中节点键</param>
        /// <param name="defValue">默认值</param>
        /// <returns></returns>
        public static void SetAppSettingValue(string key, string value)
        {
            ConfigurationManager.AppSettings[key] = value;
        }

        public static string GetSystemTitle()
        {
            return GetAppSettingValue(ConstKey.SystemTitle, "会员管理系统v1.0");
        }

        /// <summary>
        /// 输出数据到Excel
        /// </summary>
        /// <param name="data">数据</param>
        /// <param name="columns">列字段以及列名</param>
        /// <param name="savePath">保存路径</param>
        /// <returns>成功TRUE/失败FALSE</returns>
        public static bool ExportExcel<T>(List<T> data,Dictionary<string,string> columns,string savePath)
        {
            bool succeed = true;
            try
            {
                ClearStyle();
                //创建工作表
                ISheet sheet = null;
                //创建工作薄
                IWorkbook wbook = CreateWorkBook(savePath, null);
                sheet = wbook.CreateSheet("Sheet1");           //创建工作表
                int rowIndex = 0;
               
                if(data.Count <= 0) throw new Exception("无可导出的数据");
                int rowCount = data.Count;          //获取行数
                //设置表头
                IRow headerRow = sheet.CreateRow(rowIndex);
                ICell headerCell = null;
                for (int i = 0; i < columns.Count; i++)
                {
                    var item = columns.ElementAt(i);
                    headerCell = headerRow.CreateCell(i);
                    headerCell.SetCellValue(item.Value);
                    ICellStyle style = GetHeaderStyle(wbook);
                    headerCell.CellStyle = style;
                    sheet.SetColumnWidth(i, 100 * 60);
                    headerCell.Row.Height = 100 * 3;
                }

                //DataGridView有多少行
                IRow row = null;
                ICell cell;
                rowIndex++;
                Type dataType = typeof(T);
                for (; rowIndex <= rowCount; rowIndex++)
                {
                    var dataRow = data[rowIndex - 1];
                    row = sheet.CreateRow(rowIndex);
                    for (int colIndex = 0; colIndex < columns.Count; colIndex++)
                    {
                        var column = columns.Keys.ElementAt(colIndex);
                        var prop = dataType.GetProperty(column);
                        if (prop != null)
                        {
                            cell = row.CreateCell(colIndex);     //创建单元格  
                            cell.CellStyle = GetDefaultStyle(wbook);
                            var value = prop.GetValue(dataRow, null);
                            SetCellValue(cell, value);
                        }

                    }
                }
                //保存的文件流保存或创建
                FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate);
                wbook.Write(fs);   //写入文件                
                fs.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return succeed;
        }

        private static void SetCellValue(ICell cell,object value)
        {
            if (value != null && value != DBNull.Value)
            {
                Type type = value.GetType();
                //写入数据
                if (type == typeof(Int32))
                {
                    int.TryParse(value.ToString(), out int result);
                    cell.SetCellValue(result);
                }
                else if (type == typeof(Double))
                {
                    double.TryParse(value.ToString(), out double result);
                    cell.SetCellValue(result);
                }
                else if (type == typeof(float))
                {
                    float.TryParse(value.ToString(), out float result);
                    cell.SetCellValue(result);
                }
                else if (type == typeof(DateTime))
                {
                    cell.SetCellValue(((DateTime)value).ToString("yyyy-MM-dd HH:mm"));
                }
                else if (type == typeof(Boolean))
                {
                    cell.SetCellValue((bool)value);
                }
                else if (type == typeof(Decimal))
                {
                    cell.SetCellValue(Convert.ToDouble(value));
                }
                else if (type == typeof(float))
                {
                    cell.SetCellValue((float)value);
                }
                else
                {
                    cell.SetCellValue(value.ToString());
                }
            }
        }

        #region 样式
        private static void ClearStyle()
        {
            _defaultStyle = null;
            _headerStyle = null;
            _font = null;
        }
        private static ICellStyle _defaultStyle;
        private static ICellStyle GetDefaultStyle(IWorkbook wbook)
        {
            if (_defaultStyle == null)
            {
                _defaultStyle = wbook.CreateCellStyle();
                _defaultStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                _defaultStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                _defaultStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                _defaultStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            }
            return _defaultStyle;
        }

        private static ICellStyle _headerStyle;
        private static ICellStyle GetHeaderStyle(IWorkbook wbook, bool isBoldFont = true)
        {
            if (_headerStyle == null)
            {
                _headerStyle = wbook.CreateCellStyle();
                _headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                _headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                _headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                _headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                _headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
                _headerStyle.FillPattern = FillPattern.SolidForeground;
                if (isBoldFont)
                {
                    _headerStyle.SetFont(GetBoldFont(wbook));
                }
            }
            return _headerStyle;
        }
        private static IFont _font;
        private static IFont GetBoldFont(IWorkbook wbook)
        {
            if (_font == null)
            {
                _font = wbook.CreateFont();
                _font.FontHeight = 100 * 2.4;
                _font.IsBold = true;
            }
            return _font;
        }
        #endregion
        /// <summary>
        /// 根据文件格式返回表文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="fs">文件流</param>
        /// <returns>IWorkBook 表</returns>
        private static IWorkbook CreateWorkBook(string filePath, FileStream fs)
        {
            //读取用户选择的保存格式
            string extesion = Path.GetExtension(filePath).ToLower();
            IWorkbook wbook;
            if (extesion.Equals(".xlsx"))
            {
                if (null != fs)
                {
                    wbook = new XSSFWorkbook(fs);
                }
                else
                {
                    wbook = new XSSFWorkbook();
                }
            }
            else if (extesion.Equals(".xls"))
            {
                if (null != fs)
                {
                    wbook = new HSSFWorkbook(fs);
                }
                else
                {
                    wbook = new HSSFWorkbook();
                }
            }
            else    //如果不是上述两种格式,因为设置了过滤器几乎不会出现下面的情况
            {
                throw new Exception("文件格式不正确!");
            }
            return wbook;
        }
    }
}
